<?php

namespace App\Http\Controllers\Attendance;

use App\Http\Controllers\Controller;
use App\Models\Attendance\AttendanceData;
use App\Models\Personnel\PersonnelCleaner;
use Illuminate\Database\Query\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class AttendancePersonnelReportController extends Controller
{
    public function attendancePersonnelReportList(Request $request)
    {
        $params = $request->all();
        if (isset($params['start_date'])) {
            $params['start_date'] = date('Y-m-d', strtotime($params['start_date']));
        } else {
            $params['start_date'] = date('Y-m-d');
        }
        if (isset($params['end_date'])) {
            $params['end_date'] = date('Y-m-d', strtotime($params['end_date']));
        } else {
            $params['end_date'] = date('Y-m-d');
        }

        $query = DB::table('akbus_driver_attendance_collect')
            ->select('line', 'driver', DB::raw('SUM(normalTime) as normalTime'), DB::raw('SUM(overTime) as overTime'), DB::raw('SUM(nightOverTime) as nightOverTime'), DB::raw('SUM(leaveTime) as leaveTime'), DB::raw('SUM(fixTime) as fixTime'), DB::raw('SUM(stopTime) as stopTime'),DB::raw('SUM(checkGas) as checkGas'))
            ->where('is_del', 0)
            ->whereBetween('signDate', [$params['start_date'], $params['end_date']]);
        if (isset($params['driver'])) {
            $query->where('driver', 'like', '%' . $params['driver'] . '%');
        }
        if (isset($params['line']) && $params['line'] !== '-1') {
            $query->where('line', $params['line']);
        }
        $data = $query->orderBy('line')->orderBy('driver')->groupBy('line', 'driver')->get()->toArray();
        $result = [
            'lists' => $data,
            'start_date' => $params['start_date'],
            'end_date' => $params['end_date'],
            'line' => isset($params['line']) ? $params['line'] : -1,
            'driver' => isset($params['driver']) ? $params['driver'] : '',
        ];
        return response()->formatJson($result);
    }
    /**
     * 导出驾驶员考勤统计表
     *
     * @param  Request  $request
     *
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportAttendancePersonnelList(Request $request)
    {
        $params = $request->all();

        if (isset($params['start_date'])) {
            $params['start_date'] = date('Y-m-d', strtotime($params['start_date']));
        } else {
            $params['start_date'] = date('Y-m-d');
        }
        if (isset($params['end_date'])) {
            $params['end_date'] = date('Y-m-d', strtotime($params['end_date']));
        } else {
            $params['end_date'] = date('Y-m-d');
        }

        $query = DB::table('akbus_driver_attendance_collect')
            ->select('line', 'driver', DB::raw('SUM(normalTime) as normalTime'), DB::raw('SUM(overTime) as overTime'), DB::raw('SUM(nightOverTime) as nightOverTime'), DB::raw('SUM(leaveTime) as leaveTime'), DB::raw('SUM(fixTime) as fixTime'), DB::raw('SUM(stopTime) as stopTime'),DB::raw('SUM(checkGas) as checkGas'))
            ->where('is_del', 0)
            ->whereBetween('signDate', [$params['start_date'], $params['end_date']]);
        if (isset($params['driver'])) {
            $query->where('driver', 'like', '%' . $params['driver'] . '%');
        }
        if (isset($params['line']) && $params['line'] !== '-1') {
            $query->where('line', $params['line']);
        }
        $data = $query->orderBy('line')->orderBy('driver')->groupBy('line', 'driver')->get()->toArray();

        $line = DB::table('akbus_bus_distribution')->groupBy('line')->get()->toArray();
        $month = date('Ymd', strtotime($params['start_date'])) . '-' . date('Ymd', strtotime($params['end_date']));
        $title = '安康市公共交通责任有限公司驾驶员考勤(' . $month . ')';

        // 创建一个Spreadsheet对象
        $spreadsheet = new Spreadsheet();
        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Maardten Balliauw')
            ->setLastModifiedBy('Maarten Balliauw')
            ->setTitle('Office 2007 XLSX Test Document')
            ->setSubject('Office 2007 XLSX Test Document')
            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
            ->setKeywords('office 2007 openxml php')
            ->setCategory('Test result file');
        //根据线路导出
        foreach ($line as $k => $v) {
            if ($k > 0) {
                $spreadsheet->createSheet();
            }
            //添加表头
            $spreadsheet->setActiveSheetIndex($k)
                ->setCellValue('A1', $title)
                ->setCellValue('A2', '姓名')
                ->setCellValue('B2', '小计')
                ->setCellValue('C2', '加班')
                ->setCellValue('D2', '修车')
                ->setCellValue('E2', '停运')
                ->setCellValue('F2', '晚上加班')
                ->setCellValue('G2', '检气瓶');
            $num = 3;
            for ($i = 0, $iMax = count($data); $i < $iMax; $i++) {
                if ($v['line'] === $data[$i]['line']) {
                    //防止出现NULL值 从而导致导出数据变为科学计数法
                    $data[$i]['normalTime'] = $data[$i]['normalTime'] ?: 0;
                    $data[$i]['overTime'] = $data[$i]['overTime'] ?: 0;
                    $data[$i]['fixTime'] = $data[$i]['fixTime'] ?: 0;
                    $data[$i]['stopTime'] = $data[$i]['stopTime'] ?: 0;
                    $data[$i]['nightOverTime'] = $data[$i]['nightOverTime'] ?: 0;
                    $data[$i]['checkGas'] = $data[$i]['checkGas'] ?: 0;
                    $spreadsheet->getActiveSheet()->setCellValue('A' . $num, $data[$i]['driver']);
                    $spreadsheet->getActiveSheet()->setCellValue('B' . $num, $data[$i]['normalTime']);
                    $spreadsheet->getActiveSheet()->setCellValue('C' . $num, $data[$i]['overTime']);
                    $spreadsheet->getActiveSheet()->setCellValue('D' . $num, $data[$i]['fixTime']);
                    $spreadsheet->getActiveSheet()->setCellValue('E' . $num, $data[$i]['stopTime']);
                    $spreadsheet->getActiveSheet()->setCellValue('F' . $num, $data[$i]['nightOverTime']);
                    $spreadsheet->getActiveSheet()->setCellValue('G' . $num, $data[$i]['checkGas']);
                    $num++;
                }
            }
            $spreadsheet->getActiveSheet()->setCellValue('A' . ($num + 1), '总合计:');
            //写入计算公式
            $array = ['B', 'C', 'D', 'E', 'F','G'];
            foreach ($array as  $value) {
                $spreadsheet->getActiveSheet()->setCellValue($value . ($num + 1), '=SUM(' . $value . '3:' . $value . $num . ')');
                $spreadsheet->getActiveSheet()->getCell($value . ($num + 1))->getCalculatedValue();
            }
            // 合并行、列
            $spreadsheet->getActiveSheet()->mergeCells('A1:G1')->mergeCells('A' . $num . ':G' . $num);
            //  设置宽度
            $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);
            // 设置高度
            $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(38);
            $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(38);
            //居中
            $numberStyleCenter = [
                'alignment' => [
                    'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    'vertical'   => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
                ],
            ];
            $spreadsheet->getActiveSheet()->getStyle('A1:G2')->applyFromArray($numberStyleCenter)
                ->getAlignment()->setWrapText(true);
            //设置字体
            $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
            $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            $spreadsheet->getActiveSheet()->getStyle('A2:G2')->getFont()->setBold(true);
            //加边框
            $borderStyleArray = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color'       => ['argb' => '000000000'],
                    ],
                ],
            ];
            $spreadsheet->getActiveSheet()->getStyle('A1:G' . ($num + 1))->applyFromArray($borderStyleArray);
            $c = $v['line'] ? $v['line'] . '路' : '停运';

            // 重命名 worksheet
            $spreadsheet->getActiveSheet()->setTitle($c);
        }
        // 将活动工作表索引设置为第一个工作表，以便Excel将其作为第一个工作表打开
        $spreadsheet->setActiveSheetIndex(0);
        // 将输出重定向到客户端的Web浏览器 (Xlsx)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $new_title = '驾驶员考勤统计' . $month;
        header('Content-Disposition: attachment;filename="' . $new_title . '.xlsx"');
        header('Cache-Control: max-age=0');
        // 如果正在使用IE 9
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }
}
